<?php
/*
Primary Developer: Alex Arey
Secondary: Derek Shue
Updated: 12/10/2011
Module Description: CoursesQuery provides an interface for Course-related database queries.
*/
class Application_Model_InternshipQuery
{
    public function init(){

	}
	
	/**
	* Selects internships for the default view of students browsing internships
	* ie returns accepted internships for the current term
	*/
	public function getInternships_Student(){	
		$term = new Application_Model_Term();
		$current_term = $term->getCurrentOpenTermStr('stuapp');
		$positionModel = new Application_Model_DbTable_InternPositions();
		$query = 'TERM = \'' . $current_term . '\' and ACCEPTED = \'y\'';
		try{
			$rows = $positionModel->fetchAll($query)->toArray();
		}
		catch(Exception $e){
			$rows = Null;
		}
		return $rows;
	}
	
	/**
	* Selects internships for the default view of students browsing internships
	* ie returns accepted internships for the current term and where CPID = $partnerID
	*/
	public function getInternships_CP($partnerID){
		$term = new Application_Model_Term();
		$current_term = $term->getCurrentOpenTermStr('cpintern');
		$positionModel = new Application_Model_DbTable_InternPositions();
		$query = 'TERM = \'' . $current_term . '\' ';
		if($partnerID){
			$query = $query . "and CPID =  $partnerID";
		}
		try{
			$rows = $positionModel->fetchAll($query)->toArray();
		}
		catch(Exception $e){
			$rows = Null;
		}
		return $rows;
    }
	
	/**
	* Selects internships for the student in the student browse/search internships view
	* Term and Termmodifier behave as specified in Application_Model_Term
	* Name is case-insensitive compare.
	*/
	public function selectInternships_Student($term, $termModifier, $name){
		$terms = new Application_Model_Term();
		try{
		   $term_list = $terms->getTerms($term, $termModifier);
		}
		catch(Exception $e){
			return Null;
		}
		$positionModel = new Application_Model_DbTable_InternPositions();
		
		$query = 'TERM in ' . $term_list . 'and upper(NAME) like \'%'.strtoupper($name).'%\'';

		try{
			$rows = $positionModel->fetchAll($query)->toArray();
		}
		catch(Exception $e){
			$rows = Null;
		}
		return $rows;
    }
	
	/**
	* Selects internship requests for the current term.
	* This is the data for the default view of Admin/Review Internships
	*/
	public function getInternships_AD(){
		$term = new Application_Model_Term();
		$current_term = $term->getCurrentFutureTermsStr_admin();
		$positionModel = new Application_Model_DbTable_InternPositions();
		$query = 'TERM in ' . $current_term;
		try{
			$rows = $positionModel->fetchAll($query)->toArray();
		}
		catch(Exception $e){
			$rows = Null;
		}
		return $rows;
    }
	
	/**
	*	Selects search results for community partner internships requests.
	* 	term and termmodifier behave as defined in Application_Model_Term
	*	will return internships only where CPID = $partnerID
	*   returns an array of rows.
	*/
    public function selectInternships_CP($partnerID, $term, $termModifier, $name){
		$terms = new Application_Model_Term();
		try{
		   $term_list = $terms->getTerms($term, $termModifier);
		}
		catch(Exception $e){
			return Null;
		}
		$positionModel = new Application_Model_DbTable_InternPositions();
		$query = 'TERM in ' . $term_list . ' and CPID = ' . $partnerID . ' and upper(NAME) like \'%'.strtoupper($name).'%\'';
		try{
			$rows = $positionModel->fetchAll($query)->toArray();
		}
		catch(Exception $e){
			$rows = Null;
		}
		return $rows;
    }
    
	/**
	* Selects the specified internship position where POSID = $posid
	*/
    public function selectInternship_ID($posid){
		$positionModel = new Application_Model_DbTable_InternPositions();
		$query = 'POSID = ' . $posid;
		try{
			$row = $positionModel->fetchRow($query);
			if($row){
				$row = $row->toArray();
			}
		}
		catch(Exception $e){
			$row = Null;
		}
		return $row;
    }
    
	/**
	* selects all applications for the given intership position.
	* Returns as an array of table rows.
	*/
    public function selectApplicationsForInternship_CP($posid){
		$applicationModel = new Application_Model_DbTable_ApplicationAcceptance();
		$query = 'POSID = ' . $posid;
		try{
			$rows = $applicationModel->fetchAll($query);
			if($rows){
				$rows = $rows->toArray();
			}
		}
		catch(Exception $e){
			$rows = Null;
		}
		return $rows;
    }	
    
    /**
    * Selects all current term student intership applications for the administrator view.
    * returns as an array of table rows.
    */
    public function selectCurrentApplications_AD(){
    	$terms = new Application_Model_Term();
		$applicationModel = new Application_Model_DbTable_Applications();
		$query = 'TERM in '. $terms->getCurrentFutureTermsStr_admin();
		try{
			$rows = $applicationModel->fetchAll($query);
			if($rows){
				$rows = $rows->toArray();
			}
		}
		catch(Exception $e){
			$rows = Null;
		}
		return $rows;
    }
    /**
    * Selects all student intership applications for the administrator view.
    * returns as an array of table rows.
    */
    public function selectApplications_AD(){
    	$terms = new Application_Model_Term();
    	$applicationModel = new Application_Model_DbTable_Applications();
    	//$query = 'TERM in '. $terms->getCurrentFutureTermsStr_admin();
    	try{
    		$rows = $applicationModel->fetchAll();
    		if($rows){
    			$rows = $rows->toArray();
    		}
    	}
    	catch(Exception $e){
    		$rows = Null;
    	}
    	return $rows;
    }
    
    /**
    * returns the count of all suggested positions for the internship position
    * where the community partner has accepted and the student has not deffered 
    */
    public function countPositionsOccupied_CP($posid){
    	$table = new Application_Model_DbTable_ApplicationAcceptance();
    	$where = 'POSID = '.$posid.' and ACCEPTED in (\'c\',\'s\')';
		$accepted = $table->getSuggestedPositions($where);
		return count($accepted);
    }
    /**
    * returns true iff the count of all suggested positions for the internship position
    * where the community partner has accepted and the student has not deffered 
    * is less than the number of positions granted by the admins
    */
    public function positionsAvailable($posid){
    	$table = new Application_Model_DbTable_InternPositions();
    	$where = 'POSID = '.$posid;
    	$pos = $table->getInternPosition($where);
    	return ($pos['POSITIONSGRANTED'] - $this->countPositionsOccupied_CP($posid));
    }
    
    public function getPartnerNameForPID($posid){
    	$posModel = new Application_Model_DbTable_InternPositions();
    	$cpModel = new Application_Model_DbTable_Organizations();
    	try{
    		$prow = $posModel->getInternPosition('POSID = '.$posid);
    		$cpid = $prow['CPID'];
    		$prow = $cpModel->getOrganization('ID = '.$cpid);
    	}
    	catch(Exception $e){
    		$prow = Null;
    	}
    	return $prow['NAME'];
    }
}
?>
